﻿//--------------------------------------------------------------------------
// <copyright file="SqlHelper.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
// <summary>SqlHelper</summary>
//--------------------------------------------------------------------------
namespace MS.Msn.MediaSolutions.Common
{
    using System;
    using System.Data.SqlClient;
    using System.Data;
    using System.Xml;
    using System.Collections;
    using System.Configuration;

    /// <summary>
    /// SqlHelper
    /// </summary>
    public class SqlHelper : IDisposable
    {
        #region Fields
        /// <summary>
        /// Sql Transaction Key
        /// </summary>
        private const string TransName = "SqlTrans";

        /// <summary>
        /// SqlConnection
        /// </summary>
        private SqlConnection conn;

        /// <summary>
        /// SqlCommand
        /// </summary>
        private SqlCommand cmd;

        /// <summary>
        /// SqlTransaction
        /// </summary>
        private SqlTransaction trans;

        /// <summary>
        /// SqlDataReader
        /// </summary>
        private SqlDataReader reader;

        /// <summary>
        /// Sql Return Parameter
        /// </summary>
        private SqlParameter sqlReturnParam;

        /// <summary>
        /// Sql Output Parameter
        /// </summary>
        private SqlParameter sqlOutputParam;

        #endregion

        #region Constructor
        /// <summary>
        /// SqlHelper
        /// </summary>
        public SqlHelper()
        {
            string sc = ConfigurationManager.ConnectionStrings["DefaultDbConnection"].ConnectionString;
            this.conn = new SqlConnection(sc);
            this.Init();
        }

        /// <summary>
        /// SqlHelper
        /// </summary>
        /// <param name="connectionString">Connection String</param>
        public SqlHelper(string connectionString)
        {
            this.conn = new SqlConnection(connectionString);
            this.Init();
        }

        /// <summary>
        /// SqlHelper
        /// </summary>
        /// <param name="connection">SqlConnection</param>
        public SqlHelper(SqlConnection connection)
        {
            this.conn = connection;
            this.Init();
        }

        /// <summary>
        /// dispose
        /// </summary>
        ~SqlHelper()
        {
            this.Dispose(false);
        }
        #endregion

        #region Property
        /// <summary>
        /// SqlCommand
        /// </summary>
        public SqlCommand Command
        {
            get { return this.cmd; }
        }

        /// <summary>
        /// SqlDataReader
        /// </summary>
        public SqlDataReader SqlRead
        {
            get
            {
                if (this.reader != null)
                {
                    return this.reader;
                }

                this.reader = this.cmd.ExecuteReader();
                return this.reader;
            }
        }

        /// <summary>
        /// Get Return Value
        /// </summary>
        public object ReturnValue
        {
            get
            {
                return this.sqlReturnParam.Value;
            }
        }

        /// <summary>
        /// Get Output Value
        /// </summary>
        public object OutputValue
        {
            get
            {
                return this.sqlOutputParam.Value;
            }
        }
        #endregion

        #region Static Method
        /// <summary>
        /// String To SqlDbType
        /// </summary>
        /// <param name="type">SqlDbType string</param>
        /// <returns>SqlDbType</returns>
        public static SqlDbType GetSqlDBType(string type)
        {
            return (SqlDbType)Enum.Parse(typeof(SqlDbType), type, true);
        }

        /// <summary>
        /// SqlDbType To CsType
        /// </summary>
        /// <param name="type">sqlDbType</param>
        /// <returns>CsharpType</returns>
        public static Type GetType(SqlDbType type)
        {
            switch (type)
            {
                case SqlDbType.BigInt:
                    return typeof(Int64);
                case SqlDbType.Binary:
                    return typeof(byte[]);
                case SqlDbType.Bit:
                    return typeof(byte);
                case SqlDbType.Char:
                    return typeof(char);
                case SqlDbType.DateTime:
                    return typeof(DateTime);
                case SqlDbType.Decimal:
                    return typeof(Decimal);
                case SqlDbType.Float:
                    return typeof(float);
                case SqlDbType.Image:
                    return typeof(byte[]);
                case SqlDbType.Int:
                    return typeof(int);
                case SqlDbType.Money:
                    return typeof(Decimal);
                case SqlDbType.NChar:
                    return typeof(string);
                case SqlDbType.NText:
                    return typeof(string);
                case SqlDbType.NVarChar:
                    return typeof(string);
                case SqlDbType.Real:
                    return typeof(float);
                case SqlDbType.SmallDateTime:
                    return typeof(DateTime);
                case SqlDbType.SmallInt:
                    return typeof(Int16);
                case SqlDbType.SmallMoney:
                    return typeof(Decimal);
                case SqlDbType.Text:
                    return typeof(string);
                case SqlDbType.Timestamp:
                    return typeof(byte[]);
                case SqlDbType.TinyInt:
                    return typeof(byte);
                case SqlDbType.Udt:
                    return typeof(string);
                case SqlDbType.UniqueIdentifier:
                    return typeof(Guid);
                case SqlDbType.VarBinary:
                    return typeof(byte[]);
                case SqlDbType.VarChar:
                    return typeof(string);
                case SqlDbType.Variant:
                    return typeof(object);
                case SqlDbType.Xml:
                    return typeof(System.Xml.XmlDocument);
                default:
                    return typeof(string);
            }
        }

        /// <summary>
        /// HasSize
        /// </summary>
        /// <param name="type">sqlDbType</param>
        /// <returns>bool</returns>
        public static bool HasSize(SqlDbType type)
        {
            switch (type)
            {
                case SqlDbType.BigInt:
                    return false;
                case SqlDbType.Binary:
                    return true;
                case SqlDbType.Bit:
                    return false;
                case SqlDbType.Char:
                    return true;
                case SqlDbType.DateTime:
                    return false;
                case SqlDbType.Decimal:
                    return true;
                case SqlDbType.Float:
                    return false;
                case SqlDbType.Image:
                    return false;
                case SqlDbType.Int:
                    return false;
                case SqlDbType.Money:
                    return false;
                case SqlDbType.NChar:
                    return true;
                case SqlDbType.NText:
                    return false;
                case SqlDbType.NVarChar:
                    return true;
                case SqlDbType.Real:
                    return false;
                case SqlDbType.SmallDateTime:
                    return false;
                case SqlDbType.SmallInt:
                    return false;
                case SqlDbType.SmallMoney:
                    return false;
                case SqlDbType.Text:
                    return false;
                case SqlDbType.Timestamp:
                    return false;
                case SqlDbType.TinyInt:
                    return false;
                case SqlDbType.Udt:
                    return false;
                case SqlDbType.UniqueIdentifier:
                    return false;
                case SqlDbType.VarBinary:
                    return true;
                case SqlDbType.VarChar:
                    return true;
                case SqlDbType.Variant:
                    return false;
                case SqlDbType.Xml:
                    return false;
                default:
                    return false;
            }
        }

        #region ExecuteNonQuery
        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters)
        /// the connection string
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery("sp_table_select");
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteNonQuery(commandText, CommandType.StoredProcedure, commandParameters);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery("sp_table_select", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            using (SqlHelper helper = new SqlHelper())
            {
                helper.PopulateSqlParameters(commandParameters);
                return helper.ExecuteNonQuery(commandText, commandType);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters)
        /// the connection string
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery("sp_table_select");
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <param name="parameterValues">An array of Objects used to execute the command</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string commandText, params object[] parameterValues)
        {
            return SqlHelper.ExecuteNonQuery(commandText, CommandType.StoredProcedure, parameterValues);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery("sp_table_select", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="parameterValues">An array of Objects used to execute the command</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string commandText, CommandType commandType, params object[] parameterValues)
        {
            using (SqlHelper helper = new SqlHelper())
            {
                helper.PopulateSqlParameters(parameterValues);
                return helper.ExecuteNonQuery(commandText, commandType);
            }
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            using (SqlHelper helper = new SqlHelper())
            {
                helper.PopulateSqlParameters(commandParameters);
                return helper.ExecuteScalar(commandText, commandType);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteScalar(commandText, CommandType.StoredProcedure, commandParameters);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="parameterValues">An array of Objects used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(string commandText, CommandType commandType, params object[] parameterValues)
        {
            using (SqlHelper helper = new SqlHelper())
            {
                helper.PopulateSqlParameters(parameterValues);
                return helper.ExecuteScalar(commandText, commandType);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <param name="parameterValues">An array of Objects used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(string commandText, params object[] parameterValues)
        {
            return SqlHelper.ExecuteScalar(commandText, CommandType.StoredProcedure, parameterValues);
        }
        #endregion

        #region ExecuteScalarIdentity
        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 identity resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalarIdentity(string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteScalarIdentity(commandText, CommandType.StoredProcedure, commandParameters);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 identity resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalarIdentity(string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            using (SqlHelper helper = new SqlHelper())
            {
                helper.PopulateSqlParameters(commandParameters);
                return helper.ExecuteScalarIdentity(commandText, commandType);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 identity resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <param name="parameterValues">An array of Objects used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalarIdentity(string commandText, params object[] parameterValues)
        {
            return SqlHelper.ExecuteScalarIdentity(commandText, CommandType.StoredProcedure, parameterValues);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 identity resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="parameterValues">An array of Objects used to execute the command</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalarIdentity(string commandText, CommandType commandType, params object[] parameterValues)
        {
            using (SqlHelper helper = new SqlHelper())
            {
                helper.PopulateSqlParameters(parameterValues);
                return helper.ExecuteScalarIdentity(commandText, commandType);
            }
        }
        #endregion
        #endregion

        #region Transaction
        /// <summary>
        /// Begin Transaction
        /// </summary>
        public void BeginTransaction()
        {
            this.trans = this.conn.BeginTransaction(TransName);
            this.cmd.Transaction = this.trans;
        }

        /// <summary>
        /// Begin Transaction
        /// </summary>
        /// <param name="sqlCommand">SqlCommand</param>
        public void BeginTransaction(SqlCommand sqlCommand)
        {
            if (sqlCommand == null)
            {
                return;
            }

            this.cmd = sqlCommand;
            this.BeginTransaction();
        }

        /// <summary>
        /// Commit
        /// </summary>
        public void Commit()
        {
            this.trans.Commit();
        }

        /// <summary>
        /// Rollback
        /// </summary>
        public void Rollback()
        {
            this.trans.Rollback(TransName);
        }
        #endregion

        #region Get Output Param
        /// <summary>
        /// Add Output Value
        /// </summary>
        /// <param name="paramterName">paramter Name</param>
        /// <param name="type">sql Db type</param>
        public void AddOutputParameter(string paramterName, SqlDbType type)
        {
            this.sqlOutputParam = new SqlParameter();
            this.sqlOutputParam.ParameterName = paramterName;
            this.sqlOutputParam.Direction = ParameterDirection.Output;
            this.sqlOutputParam.SqlDbType = type;
            this.cmd.Parameters.Add(this.sqlOutputParam);
        }
        #endregion

        #region Get Return Value
        /// <summary>
        /// Add Return Value
        /// </summary>
        public void AddReturnValue()
        {
            this.sqlReturnParam = new SqlParameter();
            this.sqlReturnParam.Direction = ParameterDirection.ReturnValue;
            this.cmd.Parameters.Add(this.sqlReturnParam);
        }

        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters)
        /// the connection string
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery("sp_table_select");
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public int ExecuteNonQuery(string commandText)
        {
            return this.ExecuteNonQuery(commandText, CommandType.StoredProcedure);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery("sp_table_select", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public int ExecuteNonQuery(string commandText, CommandType commandType)
        {
            this.cmd.CommandText = commandText;
            this.cmd.CommandType = commandType;
            return this.cmd.ExecuteNonQuery();
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public object ExecuteScalar(string commandText, CommandType commandType)
        {
            this.cmd.CommandText = commandText;
            this.cmd.CommandType = commandType;
            return this.cmd.ExecuteScalar();
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public object ExecuteScalar(string commandText)
        {
            return this.ExecuteScalar(commandText, CommandType.StoredProcedure);
        }
        #endregion

        #region ExecuteScalarIdentity
        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 identity resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public object ExecuteScalarIdentity(string commandText)
        {
            return this.ExecuteScalarIdentity(commandText, CommandType.StoredProcedure);
        }

        /// <summary>
        /// Execute a SqlCommand (that returns a 1x1 identity resultset and takes no parameters)
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar("sp_GetOrderCount", CommandType.StoredProcedure);
        /// </remarks>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
        public object ExecuteScalarIdentity(string commandText, CommandType commandType)
        {
            this.ExecuteNonQuery(commandText, commandType);
            return this.ExecuteScalar("SELECT @@IDENTITY", CommandType.Text);
        }
        #endregion

        #region ExecuteXmlReader
        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset).
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  XmlReader r = ExecuteXmlReader("sp_GetOrders");
        /// </remarks>
        /// <param name="commandText">The name of the stored procedure using "FOR XML AUTO"</param>
        /// <returns>An XmlReader containing the resultset generated by the command</returns>
        public XmlReader ExecuteXmlReader(string commandText)
        {
            return this.ExecuteXmlReader(commandText, CommandType.StoredProcedure);
        }

        /// <summary>
        /// Execute a stored procedure via a SqlCommand (that returns a resultset).
        /// </summary>
        /// <remarks>
        /// This method provides no access to output parameters or the stored procedure's return value parameter.
        /// 
        /// e.g.:  
        ///  XmlReader r = ExecuteXmlReader("sp_GetOrders");
        /// </remarks>
        /// <param name="commandText">The name of the stored procedure using "FOR XML AUTO" or T-SQL command</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <returns>An XmlReader containing the resultset generated by the command</returns>
        public XmlReader ExecuteXmlReader(string commandText, CommandType commandType)
        {
            this.cmd.CommandText = commandText;
            this.cmd.CommandType = commandType;
            return this.cmd.ExecuteXmlReader();
        }
        #endregion

        #region Get Table Schema

        /// <summary>
        /// GetTableSchema
        /// </summary>
        /// <param name="tableName">table name</param>
        /// <returns>DataTable</returns>
        public DataTable GetTableSchema(string tableName)
        {
            this.cmd.CommandText = string.Format(Apps.DefaultCulture, "SELECT * FROM {0} WHERE 1=2", tableName);
            this.cmd.CommandType = CommandType.Text;
            SqlDataReader scheamReader = this.cmd.ExecuteReader(CommandBehavior.KeyInfo);
            return scheamReader.GetSchemaTable();
        }

        #endregion

        #region IDisposable Members
        /// <summary>
        /// Dispose
        /// </summary>
        public void Dispose()
        {
            this.Dispose(true);
        }

        /// <summary>
        /// Dispose
        /// </summary>
        /// <param name="disposing">bool</param>
        protected virtual void Dispose(bool disposing)
        {
            this.cmd.Dispose();
            this.conn.Close();
            this.conn.Dispose();
            if (disposing)
            {
                GC.SuppressFinalize(this);
            }
        }
        #endregion

        #region Private Method
        /// <summary>
        /// Init Sql Helper
        /// </summary>
        private void Init()
        {
            this.cmd = this.conn.CreateCommand();
            if (this.conn.State == ConnectionState.Closed)
            {
                this.conn.Open();
            }

            this.cmd.CommandType = System.Data.CommandType.StoredProcedure;
        }

        /// <summary>
        /// Populate Sql Parameters
        /// </summary>
        /// <param name="commandParameters">Command Parameters</param>
        private void PopulateSqlParameters(params SqlParameter[] commandParameters)
        {
            this.cmd.Parameters.AddRange(commandParameters);
        }

        /// <summary>
        /// Populate Sql Parameters
        /// </summary>
        /// <param name="parameterValues">Parameter Values</param>
        private void PopulateSqlParameters(params object[] parameterValues)
        {
            SqlCommandBuilder.DeriveParameters(this.cmd);
            for (int i = 0; i < this.cmd.Parameters.Count; i++)
            {
                this.cmd.Parameters[i].Value = parameterValues[i];
            }
        }
        #endregion
    }
}